#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
SQL服务模块

提供SQL生成和执行功能，支持直接调用和HTTP客户端两种模式
"""

import asyncio
import aiohttp
import uuid
import time
import logging
import json
from typing import Optional, Dict, Any, List
from dataclasses import dataclass

logger = logging.getLogger(__name__)


@dataclass
class SQLResponse:
    """SQL响应数据类"""
    type: str
    id: str
    content: Any
    success: bool = True
    error_message: Optional[str] = None


class SQLService:
    """SQL服务 - 直接调用内部函数（推荐用于同项目内调用）"""
    
    def __init__(self):
        """初始化SQL服务"""
        from biz.core.functions import generate_sql_core
        from biz.config.settings import get_sql_context, cache
        from biz.core.sql.generator import intercept_sql
        
        self.generate_sql_core = generate_sql_core
        self.vn = get_sql_context()
        self.cache = cache
        self.intercept_sql = intercept_sql
    
    async def query_devices(self, question: str, filter_conditions: Optional[Dict] = None) -> SQLResponse:
        """一次性完成SQL生成和执行"""
        if not question or not question.strip():
            return SQLResponse(
                type="error",
                id="",
                content=None,
                success=False,
                error_message="问题描述不能为空"
            )
        
        try:
            logger.info(f"生成SQL的问题: {question}")
            
            # 第一步：生成SQL
            session_id, sql = self.generate_sql_core(
                question=question.strip(),
                id=None,
                filter_conditions=filter_conditions or {}
            )
            
            if not sql:
                return SQLResponse(
                    type="error",
                    id=session_id,
                    content=None,
                    success=False,
                    error_message="生成SQL失败"
                )
            
            # 第二步：SQL安全检查
            self.intercept_sql(sql)
            
            # 第三步：执行SQL
            df = self.vn.run_sql(sql=sql)
            
            # 处理时间戳列
            for col in df.select_dtypes(include=['datetime64']).columns:
                df[col] = df[col].dt.strftime('%Y-%m-%d %H:%M:%S')
            
            # 处理object类型的日期列
            for col in df.select_dtypes(include=['object']).columns:
                try:
                    import pandas as pd
                    sample_val = df[col].dropna().iloc[0] if not df[col].dropna().empty else None
                    if sample_val is not None and pd.to_datetime(sample_val, errors='coerce') is not pd.NaT:
                        df[col] = pd.to_datetime(df[col], errors='coerce').dt.strftime('%Y-%m-%d %H:%M:%S')
                except:
                    pass
            
            # 处理null值
            df = df.fillna('')
            
            # 缓存结果
            self.cache.set(id=session_id, field='df', value=df)
            
            # 转换为JSON格式
            content = json.loads(df.to_json(orient='records', date_format='iso', force_ascii=False))
            
            logger.info(f"执行SQL成功，返回 {len(content)} 条记录")
            
            return SQLResponse(
                type="df",
                id=session_id,
                content=content,
                success=True
            )
            
        except Exception as e:
            logger.error(f"查询设备失败: {e}", exc_info=True)
            return SQLResponse(
                type="error",
                id="",
                content=None,
                success=False,
                error_message=f"查询失败: {str(e)}"
            )


class SQLInterfaceClient:
    """SQL接口客户端 - 通过HTTP调用（用于跨服务调用，已废弃，建议使用 SQLService）"""
    
    def __init__(self, base_url: str = "http://localhost:5000", timeout: int = 60):
        """
        初始化SQL接口客户端
        
        Args:
            base_url: API服务器基础URL
            timeout: 请求超时时间（秒）
        """
        self.base_url = base_url.rstrip('/')
        self.timeout = timeout
        self.session_cache = {}
    
    async def query_devices(self, question: str) -> SQLResponse:
        """一次性完成SQL生成和执行（通过HTTP）"""
        if not question or not question.strip():
            return SQLResponse(
                type="error",
                id="",
                content=None,
                success=False,
                error_message="问题描述不能为空"
            )
        
        session_id = str(uuid.uuid4())
        
        try:
            logger.info(f"HTTP调用生成SQL: {question}")
            
            # 第一步：生成SQL
            url = f"{self.base_url}/api/v0/generate_sql"
            params = {"question": question.strip(), "id": session_id}
            
            async with aiohttp.ClientSession(timeout=aiohttp.ClientTimeout(total=self.timeout)) as session:
                async with session.get(url, params=params) as response:
                    if response.status != 200:
                        error_text = await response.text()
                        return SQLResponse(
                            type="error",
                            id=session_id,
                            content=None,
                            success=False,
                            error_message=f"生成SQL失败 ({response.status}): {error_text}"
                        )
                    
                    data = await response.json()
                    sql = data.get("text", "")
                    
                    # 缓存会话信息
                    self.session_cache[session_id] = {
                        "sql": sql,
                        "question": question,
                        "timestamp": time.time(),
                        "expires_at": time.time() + 1800
                    }
            
            # 第二步：执行SQL
            url = f"{self.base_url}/api/v0/run_sql"
            params = {"id": session_id}
            
            async with aiohttp.ClientSession(timeout=aiohttp.ClientTimeout(total=self.timeout)) as session:
                async with session.get(url, params=params) as response:
                    if response.status != 200:
                        error_text = await response.text()
                        return SQLResponse(
                            type="error",
                            id=session_id,
                            content=None,
                            success=False,
                            error_message=f"执行SQL失败 ({response.status}): {error_text}"
                        )
                    
                    data = await response.json()
                    content = data.get("df", [])
                    
                    if isinstance(content, str):
                        try:
                            content = json.loads(content)
                        except json.JSONDecodeError:
                            pass
                    
                    logger.info(f"HTTP调用执行SQL成功")
                    
                    return SQLResponse(
                        type="df",
                        id=session_id,
                        content=content,
                        success=True
                    )
                    
        except asyncio.TimeoutError:
            return SQLResponse(
                type="error",
                id=session_id,
                content=None,
                success=False,
                error_message=f"请求超时 (>{self.timeout}秒)"
            )
        except aiohttp.ClientError as e:
            return SQLResponse(
                type="error",
                id=session_id,
                content=None,
                success=False,
                error_message=f"网络请求错误: {str(e)}"
            )
        except Exception as e:
            logger.error(f"HTTP查询设备失败: {e}", exc_info=True)
            return SQLResponse(
                type="error",
                id=session_id,
                content=None,
                success=False,
                error_message=f"查询失败: {str(e)}"
            )